var sqlite3 = require('sqlite3');
let path = require('path');
const { getFileData } = require('../MyTool/myFS');
let PUBLIC_PATH = path.resolve(__dirname, '../../public');


let getSqlLiteData = function (sql, paramArr, databaseFilename = 'supplier.db') {
    return new Promise(async (resolve, reject) => {
        let connection;
        try {
            connection = await getNewConn(databaseFilename);
            result = await connection.excute(sql, paramArr);
            resolve(result);
        } catch (err) {
            reject(err);
        } finally {
            if (connection) {
                try {
                    connection.close();
                } catch (err) {
                    throw (err);
                }
            }
        }
    });
};

let getNewConn = function (databaseFilename) {
    return new Promise(async (resolve, reject) => {
        var database = await getDataBase(databaseFilename);
        database.excute = function (sql, paramArr) {
            return new Promise(async (resolve, reject) => {
                if (sql.startsWith('select') || sql.startsWith('Select') || sql.startsWith('SELECT')) {
                    let resultArr = [];
                    database.each(sql, (err, result) => {
                        if (err) {
                            reject('[SELECT ERROR] - ', err.message);
                        }
                        else {
                            resultArr.push(result);
                        }
                    }, (err, count) => {
                        if (err) {
                            reject('[SELECT err] - ', err);
                        }
                        else {
                            resolve(resultArr);
                        }
                    });
                }
                else {
                    database.run(sql, paramArr, function (result, err) {
                        if (this.changes > 0) {
                            resolve('修改成功');
                        }
                        else {
                            if (result) {
                                reject(result.message + ',修改失败');
                            }
                            else {
                                resolve('修改成功,但影响行数为' + this.changes);
                            }
                        }
                    });

                }
            });
        };
        resolve(database);
    });
};

let getDataBase = function (databaseFilename) {
    return new Promise((resolve, reject) => {
        let path = PUBLIC_PATH + "\\DataBase\\" + databaseFilename;
        var database = new sqlite3.Database(path, function (err) {
            if (err) {
                reject(err);
            }
            else {

                database.all('select * from users limit 1', (state, err) => {
                    callback(state, err);
                });

                let callback = async function (state, err) {
                    if (state && state.code == 'SQLITE_ERROR') {
                        //初始化 && 建表
                        let data = await getFileData('/DataBase/supplier_init.sql');

                        let sql = data;
                        database.exec(sql, (state, err) => {
                            resolve(database);
                        });
                    }
                    else {
                        resolve(database);
                    }
                };
            }
        });
    });
};

exports.getSqlLiteData = getSqlLiteData;
